﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Text.RegularExpressions;
using MSL.DataBase;
using System.Collections;

namespace MSL.DataBase
{
    /// <summary>
    /// 数据访问基类
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public class DataProviderBase<T> //where T : new()
    {
        #region 变量

        private string _tableName;  //表名
        private string _connString; //
        private StringBuilder _sbcommandText;//sql
        private StringBuilder _columns;//列名
        private StringBuilder _columnsValue;
        private Hashtable _htParameters;//参数
        private string _incrementPrimaryKey;//自增主键 
        #endregion

        #region 构造函数

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="incrementPrimaryKey">自增主键</param>
        public DataProviderBase(string connString, string tableName, string incrementPrimaryKey)
        {
            this._connString = connString;
            this._tableName = tableName;
            this._incrementPrimaryKey = incrementPrimaryKey;
        }
        /// <summary>
        /// 构造函数 无自增主键
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        public DataProviderBase(string connString, string tableName)
        {
            this._connString = connString;
            this._tableName = tableName;
        }

        /// <summary>
        /// 泛型实例化
        /// </summary>
        //private static T Model
        //{
        //    get
        //    {
        //        return new T();
        //    }
        //}
        #endregion

        #region 增

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model">model实体</param>
        /// <returns></returns>
        public virtual int Add(T model)
        {
            this._sbcommandText = new StringBuilder();
            this._columns = new StringBuilder();
            this._columnsValue = new StringBuilder();
            this._htParameters = new Hashtable();
            this._sbcommandText.AppendFormat("INSERT INTO {0}(", this._tableName);

            AddForParamters(model);
            this._sbcommandText.AppendFormat("{0}) VALUES ({1});", Regex.Replace(this._columns.ToString(), "^,", ""), Regex.Replace(this._columnsValue.ToString(), "^,", ""));
            return DbHelper.ExecSql(this._sbcommandText.ToString(), false, this._htParameters);
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model">model实体</param>
        /// <param name="i">返回INT型主键值</param>
        public virtual void Add(T model, out int i)
        {
            Add(null, null, model, out i);
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="model">model实体</param>
        /// <param name="i">返回INT型主键值</param>
        public virtual void Add(T model, string tableName, out int i)
        {
            Add(null, tableName, model, out i);
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="model">model实体</param>
        /// <param name="i">返回INT型主键值</param>
        public virtual void Add(string connString, string tableName, T model, out int i)
        {
            if (String.IsNullOrEmpty(connString))
            {
                connString = this._connString;
            }
            if (String.IsNullOrEmpty(tableName))
            {
                tableName = this._tableName;
            }
            this._sbcommandText = new StringBuilder();
            this._columns = new StringBuilder();
            this._columnsValue = new StringBuilder();
            this._htParameters = new Hashtable();
            this._sbcommandText.AppendFormat("INSERT INTO {0}(", tableName);
            AddForParamters(model);
            this._sbcommandText.AppendFormat("{0}) VALUES ({1});SELECT @@IDENTITY;", Regex.Replace(this._columns.ToString(), "^,", ""), Regex.Replace(this._columnsValue.ToString(), "^,", ""));
            object obj = DbHelper.ExecScalar(connString, this._sbcommandText.ToString(), false, this._htParameters);
            if (obj == null)
            {
                i = 0;
            }
            else
            {
                i = Convert.ToInt32(obj);
            }
        }
        #endregion

        #region 统计

        /// <summary>
        /// 记录总数
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int Count(string strWhere)
        {
            this._sbcommandText = new StringBuilder();
            this._sbcommandText.AppendFormat("SELECT COUNT(*) FROM {0}", this._tableName);
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.AppendFormat(" WHERE {0}", strWhere);
            }
            object obj = DbHelper.ExecScalar(this._connString, this._sbcommandText.ToString());
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }

        /// <summary>
        /// 记录总数
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int Count(string tableName, string strWhere)
        {
            return Count(null, tableName, strWhere);
        }

        /// <summary>
        /// 记录总数
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int Count(string connString, string tableName, string strWhere)
        {
            if (String.IsNullOrEmpty(connString))
            {
                connString = this._connString;
            }
            if (String.IsNullOrEmpty(tableName))
            {
                tableName = this._tableName;
            }
            this._sbcommandText = new StringBuilder();
            this._sbcommandText.AppendFormat("SELECT COUNT(*) FROM {0}", tableName);
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.AppendFormat(" WHERE {0}", strWhere);
            }
            object obj = DbHelper.ExecScalar(connString, this._sbcommandText.ToString());
            if (obj == null)
            {
                return 0;
            }
            return Convert.ToInt32(obj);
        }
        #endregion

        #region 存在

        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual bool Exists(string strWhere)
        {
            return Exists(null, null, strWhere);
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual bool Exists(string tableName, string strWhere)
        {
            return Exists(null, tableName, strWhere);
        }
        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual bool Exists(string connString, string tableName, string strWhere)
        {
            if (String.IsNullOrEmpty(connString))
            {
                connString = this._connString;
            }
            if (String.IsNullOrEmpty(tableName))
            {
                tableName = this._tableName;
            }
            this._sbcommandText = new StringBuilder();
            this._sbcommandText.AppendFormat("SELECT COUNT(1) FROM {0}", tableName);
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.AppendFormat(" WHERE {0}", strWhere);
            }
            return DbHelper.Exists(connString, this._sbcommandText.ToString());
        }
        #endregion

        #region 删除

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int DeleteList(string strWhere)
        {
            this._sbcommandText = new StringBuilder();
            this._sbcommandText.AppendFormat("DELETE FROM {0}", this._tableName);
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.Append(" WHERE " + strWhere);
            }
            return DbHelper.ExecSql(this._connString, this._sbcommandText.ToString());
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int DeleteList(string tableName, string strWhere)
        {
            return DeleteList(null, tableName, strWhere);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int DeleteList(string connString, string tableName, string strWhere)
        {
            if (String.IsNullOrEmpty(connString))
            {
                connString = this._connString;
            }
            if (String.IsNullOrEmpty(tableName))
            {
                tableName = this._tableName;
            }
            this._sbcommandText = new StringBuilder();
            this._sbcommandText.AppendFormat("DELETE FROM {0}", tableName);
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.Append(" WHERE " + strWhere);
            }
            return DbHelper.ExecSql(connString, this._sbcommandText.ToString());
        }
        #endregion

        #region 查询 实体集合

        /// <summary>
        /// DataTable转换成List
        /// </summary>
        /// <param name="dataTable">集合</param>
        /// <returns></returns>
        public virtual List<T> DataTableToList(DataTable dataTable)
        {
            List<T> list = new List<T>();
            foreach (DataRow row in dataTable.Rows)
            {
                T model = Activator.CreateInstance<T>();
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    if (!(!row.Table.Columns.Contains(info.Name) || String.IsNullOrEmpty(row[info.Name].ToString())))
                    {
                        model.GetType().GetProperty(info.Name).SetValue(model, row[info.Name], null);
                    }
                }
                list.Add(model);
            }
            return list;
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="top"></param>
        /// <param name="strWhere">条件</param>
        /// <param name="filedOrder">排序</param>
        /// <returns></returns>
        public virtual List<T> GetList(int top, string strWhere, string filedOrder)
        {
            return this.GetList(null, null, top, null, strWhere, filedOrder);
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="top"></param>
        /// <param name="strWhere">条件</param>
        /// <param name="filedOrder">排序</param>
        /// <returns></returns>
        public virtual List<T> GetList(string tableName, int top, string strWhere, string filedOrder)
        {
            return this.GetList(null, tableName, top, null, strWhere, filedOrder);
        }
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="top"></param>
        /// <param name="showFields">显示字段</param>
        /// <param name="strWhere">条件</param>
        /// <param name="filedOrder">排序</param>
        /// <returns></returns>
        public virtual List<T> GetList(string connString, string tableName, int top, string showFields, string strWhere, string filedOrder)
        {
            if (String.IsNullOrEmpty(connString))
            {
                connString = this._connString;
            }
            if (String.IsNullOrEmpty(tableName))
            {
                tableName = this._tableName;
            }
            this._sbcommandText = new StringBuilder();
            this._sbcommandText.Append("SELECT ");
            if (String.IsNullOrEmpty(showFields))
            {
                StringBuilder sbColumns = new StringBuilder();
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    PropertyInfo pi = typeof(T).GetProperty(info.Name);
                    sbColumns.AppendFormat(",{0}", info.Name);
                }
                showFields = Regex.Replace(sbColumns.ToString(), "^,", "");
            }
            if (top > 0)
            {
                this._sbcommandText.AppendFormat("TOP {0} ", top);
            }
            this._sbcommandText.AppendFormat("{1} FROM {0}", tableName, showFields);
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.Append(" WHERE " + strWhere);
            }
            if (!String.IsNullOrEmpty(filedOrder))
            {
                this._sbcommandText.Append(" ORDER BY " + filedOrder);
            }
            DataSet ds = DbHelper.Query(connString, this._sbcommandText.ToString(), "dt");
            return this.DataTableToList(ds.Tables[0]);
        }
        #endregion

        #region 获得实体

        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual T GetModel(string strWhere)
        {
            return this.GetModel(null, strWhere);
        }
        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="showFields">显示字段</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual T GetModel(string showFields, string strWhere)
        {
            return GetModel(null, null, showFields, strWhere);
        }
        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="showFields">显示字段</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual T GetModel(string tableName, string showFields, string strWhere)
        {
            return GetModel(null, tableName, showFields, strWhere);
        }
        /// <summary>
        /// 获取实体
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="showFields">显示字段</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual T GetModel(string connString, string tableName, string showFields, string strWhere)
        {
            if (String.IsNullOrEmpty(connString))
            {
                connString = this._connString;
            }
            if (String.IsNullOrEmpty(tableName))
            {
                tableName = this._tableName;
            }
            if (String.IsNullOrEmpty(showFields))
            {
                StringBuilder sbColumns = new StringBuilder();
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    PropertyInfo pi = typeof(T).GetProperty(info.Name);
                    sbColumns.AppendFormat(",{0}", info.Name);
                }
                showFields = Regex.Replace(sbColumns.ToString(), "^,", "");
            }
            this._sbcommandText = new StringBuilder();
            this._sbcommandText.AppendFormat("SELECT  TOP 1 {1} FROM {0}", tableName, showFields);
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.Append(" WHERE " + strWhere);
            }
            T model = Activator.CreateInstance<T>();
            DataSet ds = DbHelper.Query(connString, this._sbcommandText.ToString(), "ds");
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (PropertyInfo info in typeof(T).GetProperties())
                {
                    if (!showFields.ToLower().Contains(info.Name.ToLower())) continue;

                    if (!String.IsNullOrEmpty(ds.Tables[0].Rows[0][info.Name].ToString()))
                    {
                        model.GetType().GetProperty(info.Name).SetValue(model, ds.Tables[0].Rows[0][info.Name], null);
                    }
                }
                return model;
            }
            return default(T);
        }
        #endregion

        #region 更新

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="connString">链接</param>
        /// <param name="tableName">表名</param>
        /// <param name="model">实体</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int Update(string connString, string tableName, T model, string strWhere)
        {
            if (String.IsNullOrEmpty(connString))
            {
                connString = this._connString;
            }
            if (String.IsNullOrEmpty(tableName))
            {
                tableName = this._tableName;
            }
            this._sbcommandText = new StringBuilder();
            this._columns = new StringBuilder();
            this._htParameters = new Hashtable();
            this._sbcommandText.AppendFormat("UPDATE {0} SET ", tableName);
            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                PropertyInfo pi = typeof(T).GetProperty(info.Name);
                object value = pi.GetValue(model, null);
                if (value == null) continue;
                //消除自增主键影响
                if (String.IsNullOrEmpty(this._incrementPrimaryKey))
                {
                    if (pi.Name.Equals(this._incrementPrimaryKey, StringComparison.CurrentCultureIgnoreCase))
                        continue;
                }

                this._columns.AppendFormat(",{0}=@{0}", info.Name);
                this._htParameters.Add(info.Name, pi.GetValue(model, null));
            }
            this._sbcommandText.Append(Regex.Replace(this._columns.ToString(), "^,", ""));
            if (!String.IsNullOrEmpty(strWhere))
            {
                this._sbcommandText.AppendFormat(" WHERE {0}", strWhere);
            }
            return DbHelper.ExecSql(connString, this._sbcommandText.ToString(), false, this._htParameters);
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="model">实体</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int Update(string tableName, T model, string strWhere)
        {
            return Update(null, tableName, model, strWhere);
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="model">实体</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public virtual int Update(T model, string strWhere)
        {
            return Update(null, null, model, strWhere);
        }
        #endregion

        #region 私有方法

        /// <summary>
        /// 添加记录时参数设置
        /// </summary>
        /// <param name="model"></param>
        private void AddForParamters(T model)
        {
            if (model == null) return;

            foreach (PropertyInfo info in typeof(T).GetProperties())
            {
                PropertyInfo pi = typeof(T).GetProperty(info.Name);
                object value = pi.GetValue(model, null);
                if (value == null) continue;
                //消除自增主键影响
                if (String.IsNullOrEmpty(this._incrementPrimaryKey))
                {
                    if (pi.Name.Equals(this._incrementPrimaryKey, StringComparison.CurrentCultureIgnoreCase))
                        continue;
                }
                this._columns.AppendFormat(",{0}", info.Name);
                this._columnsValue.AppendFormat(",@{0}", info.Name);
                this._htParameters.Add(info.Name, pi.GetValue(model, null));
            }
        }


        #endregion
    }
    #region ##

    /// <summary>
    /// 字段列属性类
    /// </summary>
    //public class SqlField : Attribute
    //{

    //    #region 变量/属性

    //    private int _length;            //长度
    //    private string _column;         //列名
    //    private SqlDbType _sqlDbType;   //类型
    //    public int Length
    //    {
    //        get
    //        {
    //            return this._length;
    //        }
    //    }

    //    public string SqlColumn
    //    {
    //        get
    //        {
    //            return this._column;
    //        }
    //    }

    //    public SqlDbType SqlDbTypeName
    //    {
    //        get
    //        {
    //            return this._sqlDbType;
    //        }
    //    }
    //    #endregion
    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    /// <param name="column">列名</param>
    //    /// <param name="sqlDbType">字段类型</param>
    //    public SqlField(string column, SqlDbType sqlDbType)
    //    {
    //        this._column = column;
    //        this._sqlDbType = sqlDbType;
    //    }
    //    /// <summary>
    //    /// 
    //    /// </summary>
    //    /// <param name="column">列名</param>
    //    /// <param name="sqlDbType">字段类型</param>
    //    /// <param name="length">长度</param>
    //    public SqlField(string column, SqlDbType sqlDbType, int length)
    //    {
    //        this._column = column;
    //        this._sqlDbType = sqlDbType;
    //        this._length = length;
    //    }

    //}
    #endregion

}
